MySQL AND、OR、NOT 运算符

您所在的位置：网站首页 › not a and b运算 › MySQL AND、OR、NOT 运算符

MySQL AND、OR、NOT 运算符

2024-07-02 09:52| 来源: 网络整理| 查看: 265

❮ 上一节下一节 ❯ MySQL AND、OR 和 NOT 运算符 MySQL AND、OR 和 NOT 运算符

WHERE 子句可以与 AND、结合使用 OR 和 NOT 运算符。

AND 和 OR 运算符用于根据多个条件过滤记录: p>

如果由 AND 分隔的所有条件都为 TRUE，则 AND 运算符会显示一条记录。如果由 OR 分隔的任何条件为 TRUE，则 OR 运算符会显示一条记录。李>

如果条件不为真，NOT 运算符会显示一条记录。

AND 语法 SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; OR 语法 SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; NOT 语法 SELECT column1, column2, ... FROM table_name WHERE NOT condition; 演示数据库

下表显示了来自 Northwind 示例数据库的完整"Customers"表:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry 1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany 2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico 3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico 4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK 5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden 6Blauer See DelikatessenHanna MoosForsterstr. 57Mannheim68306Germany 7Blondel père et filsFrédérique Citeaux24, place KléberStrasbourg67000France 8Bólido Comidas preparadasMartín SommerC/ Araquil, 67Madrid28023Spain 9Bon app'Laurence Lebihans12, rue des BouchersMarseille13008France 10Bottom-Dollar MarketseElizabeth Lincoln23 Tsawassen Blvd.TsawassenT2F 8M4Canada 11B's BeveragesVictoria AshworthFauntleroy CircusLondonEC2 5NTUK 12Cactus Comidas para llevarPatricio SimpsonCerrito 333Buenos Aires1010Argentina 13Centro comercial MoctezumaFrancisco ChangSierras de Granada 9993México D.F.05022Mexico 14Chop-suey ChineseYang WangHauptstr. 29Bern3012Switzerland 15Comércio MineiroPedro AfonsoAv. dos Lusíadas, 23São Paulo05432-043Brazil 16Consolidated HoldingsElizabeth BrownBerkeley Gardens 12 BreweryLondonWX1 6LTUK 17Drachenblut DelikatessendSven OttliebWalserweg 21Aachen52066Germany 18Du monde entierJanine Labrune67, rue des Cinquante OtagesNantes44000France 19Eastern ConnectionAnn Devon35 King GeorgeLondonWX3 6FWUK 20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria 21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil 22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain 23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France 24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden 25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany 26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France 27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy 28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal 29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain 30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain 31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil 32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA 33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela 34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil 35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela 36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA 37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland 38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK 39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany 40 La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles 78000 France 41 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France 42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada 43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA 44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany 45 Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA 46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela 47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela 48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA 49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy 50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium 51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada 52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany 53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK 54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina 55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA 56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany 57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France 58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico 59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria 60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal 61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil 62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil 63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany 64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina 65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA 66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy 67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil 68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland 69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain 70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway 71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA 72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK 73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark 74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France 75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA 76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium 77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA 78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA 79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany 80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico 81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil 82 Trail's Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA 83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark 84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France 85 Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye Reims 51100 France 86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany 87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland 88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil 89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA 90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland 91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland AND 示例

以下 SQL 语句从"Customers"中选择Country为"Germany"且城市为"Berlin"的所有字段:

实例 SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin'; 亲自试一试 » OR 示例

以下 SQL 语句从"Customers"中选择城市为"Berlin"或"Stuttgart"的所有字段:

实例 SELECT * FROM Customers WHERE City = 'Berlin' OR City = 'Stuttgart'; 亲自试一试 »

以下 SQL 语句从"Customers"中选择所有 country 为"Germany"或"Spain"的字段:

实例 SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'Spain'; 亲自试一试 » NOT 示例

以下 SQL 语句从"Customers"中选择所有 Country 不是"Germany"的字段:

实例 SELECT * FROM Customers WHERE NOT Country = 'Germany'; 亲自试一试 » 结合 AND、OR 和 NOT

您还可以组合 AND、OR 和 NOT 运算符。

以下 SQL 语句从"Customers"中选择所有字段，其中 Country 为"Germany"且城市必须为"Berlin"或"Stuttgart"（使用括号构成复杂的表达式）:

实例 SELECT * FROM Customers WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart'); 亲自试一试 »

以下 SQL 语句从"Customers"中选择所有 Country 不是"Germany"和"USA"的字段:

实例 SELECT * FROM Customers WHERE NOT Country = 'Germany' AND NOT Country = 'USA'; 亲自试一试 » ❮ 上一节下一节 ❯

【本文地址】

MySQL AND、OR、NOT 运算符

MySQL AND、OR、NOT 运算符

今日新闻

推荐新闻